package com.flatlyapps.shoppinglist;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;

public class DatabaseHandler extends SQLiteOpenHelper {
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "shoppingListDB";

	//table to store the items and item image url
	public static abstract class ShoppingItemsTable implements BaseColumns {
		public static final String TABLE_NAME = "shoppingItemsList";
		public static final String COLUMN_NAME_NAME = "name";
		public static final String COLUMN_NAME_VALUE = "value";
		public static final String COLUMN_NAME_URL = "url";
		public static final String COLUMN_NAME_LIST_ID = "listid";
		public static final String COLUMN_NAME_POSITION = "position";
	}

	//table to store the lists of items and the settings for each list
	public static abstract class ShoppingListTable implements BaseColumns {
		public static final String TABLE_NAME = "shoppingList";
		public static final String COLUMN_NAME_FUNDS = "funds";
		public static final String COLUMN_NAME_LIST_ID = "listid";
		public static final String COLUMN_NAME_COLOUR = "colour";
		public static final String COLUMN_NAME_DATE = "date";
		public static final String COLUMN_NAME_LIST_TITLE = "title";
		public static final String COLUMN_NAME_CURRENCY = "currency";
		public static final String COLUMN_NAME_POSITION = "position";
	}

	//setting the column names
	private static final String SQL_CREATE_SHOPPING_ITEMS_TABLE = "CREATE TABLE " + ShoppingItemsTable.TABLE_NAME + " (" 
														+ ShoppingItemsTable._ID + " INTEGER PRIMARY KEY," 
														+ ShoppingItemsTable.COLUMN_NAME_NAME +" TEXT,"
														+ ShoppingItemsTable.COLUMN_NAME_VALUE  + " TEXT,"
														+ ShoppingItemsTable.COLUMN_NAME_URL  + " TEXT,"
														+ ShoppingItemsTable.COLUMN_NAME_POSITION  + " INTEGER,"
														+ ShoppingItemsTable.COLUMN_NAME_LIST_ID  + " INTEGER)";

	private static final String SQL_CREATE_SHOPPING_LIST_TABLE = "CREATE TABLE " + ShoppingListTable.TABLE_NAME + " (" 
														+ ShoppingListTable._ID + " INTEGER PRIMARY KEY," 
														+ ShoppingListTable.COLUMN_NAME_FUNDS +" TEXT,"
														+ ShoppingListTable.COLUMN_NAME_COLOUR + " INTEGER, "
														+ ShoppingListTable.COLUMN_NAME_DATE + " LONG, "
														+ ShoppingListTable.COLUMN_NAME_CURRENCY +" TEXT,"
														+ ShoppingListTable.COLUMN_NAME_LIST_TITLE + " TEXT, "
														+ ShoppingListTable.COLUMN_NAME_LIST_ID  + " INTEGER, "
														+ ShoppingListTable.COLUMN_NAME_POSITION  + " INTEGER)";

	//to make it possible to upgrade table have to delete old ones and make new ones
	private static final String SQL_DELETE_SHOPPING_ITEMS_TABLE  = "DROP TABLE IF EXISTS "
														+ ShoppingItemsTable.TABLE_NAME;

	private static final String SQL_DELETE_SHOPPING_LIST_TABLE  = "DROP TABLE IF EXISTS " 
														+ ShoppingListTable.TABLE_NAME;

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(SQL_CREATE_SHOPPING_ITEMS_TABLE);
		db.execSQL(SQL_CREATE_SHOPPING_LIST_TABLE);
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL(SQL_DELETE_SHOPPING_LIST_TABLE);
		db.execSQL(SQL_DELETE_SHOPPING_ITEMS_TABLE);
		// Create tables again
		onCreate(db);
	}
}
